﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public class LoaiXeDAO
    {
        #region SelectALLLoaiXe
        public static List<LoaiXeDTO> SelectALLLoaiXe()
        {
            List<LoaiXeDTO> list = new List<LoaiXeDTO>();
            try
            {
                DataTable dt = DataProvider.ExecuteQuery("sp_SelectALLLoaiXe");
                foreach (DataRow dr in dt.Rows)
                {
                    LoaiXeDTO lx = new LoaiXeDTO();
                    lx.MALOAI = dr["MALOAI"].ToString();
                    lx.HANGSX = dr["HANGSX"].ToString();
                    lx.TRONGTAI = int.Parse(dr["TRONGTAI"].ToString());
                    list.Add(lx);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        #endregion
        public static bool ThemLoaiXe(String hangsx, String maloai,int trongtai)
        {

            #region parameter
            SqlParameter pa0 = new SqlParameter("@hangsx", SqlDbType.VarChar);
            pa0.Value = hangsx;
            SqlParameter pa1 = new SqlParameter("@maloai", SqlDbType.VarChar);
            pa1.Value = maloai;
            SqlParameter pa2 = new SqlParameter("@trongtai", SqlDbType.Int);
            pa2.Value = trongtai;
           
            #endregion
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;
            #region begin transaction
            SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "loaixe");
            cmd.Transaction = tran;
            cmd.CommandText = "select * from LOAIXE where HANGSX=@hangsx and MALOAI=@maloai and TRONGTAI =@trongtai";
            cmd.Parameters.Add(pa0);
            cmd.Parameters.Add(pa1);
            cmd.Parameters.Add(pa2);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Close();
                cmd.Parameters.Clear();
                tran.Rollback();
                throw new Exception("Loại Xe này đã có");
            }
            dr.Close();
            cmd.Parameters.Clear();
            cmd.CommandText = "insert into LOAIXE values(@hangsx,@maloai,@trongtai)";
            cmd.Parameters.Add(pa0);
            cmd.Parameters.Add(pa1);
            cmd.Parameters.Add(pa2);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            tran.Commit();
            #endregion
            conn.Close();
            return true;
        }
        public static bool SuaLoaiXe(String hangsx, String maloai, int trongtai)
        {

            #region parameter
            SqlParameter pa0 = new SqlParameter("@hangsx", SqlDbType.VarChar);
            pa0.Value = hangsx;
            SqlParameter pa1 = new SqlParameter("@maloai", SqlDbType.VarChar);
            pa1.Value = maloai;
            SqlParameter pa2 = new SqlParameter("@trongtai", SqlDbType.Int);
            pa2.Value = trongtai;

            #endregion
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;
            #region begin transaction
            SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "loaixe");
            cmd.Transaction = tran;
            cmd.CommandText = "select * from LOAIXE where HANGSX=@hangsx and MALOAI=@maloai and TRONGTAI =@trongtai";
            cmd.Parameters.Add(pa0);
            cmd.Parameters.Add(pa1);
            cmd.Parameters.Add(pa2);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Close();
                cmd.Parameters.Clear();
                tran.Rollback();
                throw new Exception("Loại Xe này đã có");
            }
            dr.Close();
            cmd.Parameters.Clear();
            cmd.CommandText = "UPDATE LOAIXE set HANGSX=@hangsx,TRONGTAI=@trongtai where MALOAI=@maloai";
            cmd.Parameters.Add(pa0);
            cmd.Parameters.Add(pa1);
            cmd.Parameters.Add(pa2);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            tran.Commit();
            #endregion
            conn.Close();
            return true;
        }
       
        public static bool XoaLoaiXe(String Maloai)
        {

            SqlParameter pa0 = new SqlParameter("@maloai", SqlDbType.VarChar);
            pa0.Value = Maloai;
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;

            SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted, "AAA");
            cmd.Transaction = tran;
            cmd.CommandText = "DELETE FROM LICHDI Where MALOAI=@maloai";
            cmd.Parameters.Add(pa0);
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            cmd.CommandText = "DELETE FROM XE Where MALOAI=@maloai";
            cmd.Parameters.Add(pa0);
            cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();
            cmd.CommandText = "DELETE FROM KHANANG Where MALOAI=@maloai";
            cmd.Parameters.Add(pa0);
            cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();
            cmd.CommandText = "DELETE FROM LOAIXE Where MALOAI=@maloai";
            cmd.Parameters.Add(pa0);
            cmd.ExecuteNonQuery();

            cmd.Dispose();
            tran.Commit();

            conn.Close();
            return true;
        }
      
    }
}
